package com.levy;

import com.alibaba.fastjson.JSONObject;
import com.levy.utils.StringUtils1;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * Created by levy on 2018/6/6.
 */
public class FileToExcel {

    public static void main(String[] args) throws IOException {

        List<Loc> list = getLocs("G://1.txt");

        ListToExcel(list,"G://2.xlsx");

    }

    private static void ListToExcel(List<Loc> list,String path) throws IOException {
        SXSSFWorkbook wb = null;
        Sheet sheet = null;
        try {
            wb = new SXSSFWorkbook();
            sheet = wb.createSheet("location");
        } catch (Exception e) {
            e.printStackTrace();
        }

        sheet.setDefaultColumnWidth(15);
        Row row = sheet.createRow(0);

        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("name");
        row.createCell(2).setCellValue("lon");
        row.createCell(3).setCellValue("lat");
        row.createCell(4).setCellValue("pname");
        row.createCell(5).setCellValue("pcode");
        row.createCell(6).setCellValue("cityname");
        row.createCell(7).setCellValue("citycode");
        row.createCell(8).setCellValue("adname");
        row.createCell(9).setCellValue("adcode");
        row.createCell(10).setCellValue("address");
        row.createCell(11).setCellValue("type");


        int i = 1;
        for (Loc loc : list){
            Row r = sheet.createRow(i++);
            r.createCell(0).setCellValue(loc.getId());
            r.createCell(1).setCellValue(loc.getName());
            r.createCell(2).setCellValue(loc.getLon());
            r.createCell(3).setCellValue(loc.getLat());
            r.createCell(4).setCellValue(loc.getPname());
            r.createCell(5).setCellValue(loc.getPcode());
            r.createCell(6).setCellValue(loc.getCityname());
            r.createCell(7).setCellValue(loc.getCitycode());
            r.createCell(8).setCellValue(loc.getAdname());
            r.createCell(9).setCellValue(loc.getAdcode());
            r.createCell(10).setCellValue(loc.getAddress());
            r.createCell(11).setCellValue(loc.getType());
        }

        OutputStream out = new FileOutputStream(new File(path));
        wb.write(out);
        out.flush();
        out.close();
        System.out.println("over");
    }

    private static List<Loc> getLocs(String path) throws FileNotFoundException {
        List<Loc> list = new ArrayList<>();
        Scanner sc = new Scanner(new File(path));
        while (sc.hasNext()){
            String s = sc.nextLine();
            if (StringUtils1.isNotBlank(s)){
                JSONObject jsonObject = JSONObject.parseObject(s);
                Loc loc = new Loc();
                if (jsonObject!=null){
                    String id = jsonObject.getString("id");
                    if (StringUtils1.isNotBlank(id)){
                        loc.setId(id);
                    }
                    String name = jsonObject.getString("name");
                    if (StringUtils1.isNotBlank(name)){
                        loc.setName(name);
                    }
                    String location = jsonObject.getString("location");
                    if (StringUtils1.isNotBlank(location)){
                        String[] split = location.split(",");
                        if (split.length>1){
                            loc.setLon(split[0]);
                            loc.setLat(split[1]);
                        }
                    }
                    String pname = jsonObject.getString("pname");
                    if (StringUtils1.isNotBlank(pname)){
                        loc.setPname(pname);
                    }
                    String pcode = jsonObject.getString("pcode");
                    if (StringUtils1.isNotBlank(pcode)){
                        loc.setPcode(pcode);
                    }
                    String cityname = jsonObject.getString("cityname");
                    if (StringUtils1.isNotBlank(cityname)){
                        loc.setCityname(cityname);
                    }
                    String citycode = jsonObject.getString("citycode");
                    if (StringUtils1.isNotBlank(citycode)){
                        loc.setCitycode(citycode);
                    }
                    String adname = jsonObject.getString("adname");
                    if (StringUtils1.isNotBlank(adname)){
                        loc.setAdname(adname);
                    }
                    String adcode = jsonObject.getString("adcode");
                    if (StringUtils1.isNotBlank(adcode)){
                        loc.setAdcode(adcode);
                    }
                    String address = jsonObject.getString("address");
                    if (StringUtils1.isNotBlank(address)){
                        loc.setAddress(address);
                    }
                    String type = jsonObject.getString("type");
                    if (StringUtils1.isNotBlank(type)){
                        loc.setType(type);
                    }
                    list.add(loc);
                }
            }
        }
        return list;
    }







}



class Loc {
    private String id;
    private String name;
    private String lon;
    private String lat;
    private String pname;
    private String pcode;
    private String cityname;
    private String citycode;
    private String adname;
    private String adcode;
    private String address;
    private String type;

    @Override
    public String toString() {
        return "Loc{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", lon='" + lon + '\'' +
                ", lat='" + lat + '\'' +
                ", pname='" + pname + '\'' +
                ", pcode='" + pcode + '\'' +
                ", cityname='" + cityname + '\'' +
                ", citycode='" + citycode + '\'' +
                ", adname='" + adname + '\'' +
                ", adcode='" + adcode + '\'' +
                ", address='" + address + '\'' +
                ", type='" + type + '\'' +
                '}';
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getLon() {
        return lon;
    }

    public void setLon(String lon) {
        this.lon = lon;
    }

    public String getLat() {
        return lat;
    }

    public void setLat(String lat) {
        this.lat = lat;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public String getPcode() {
        return pcode;
    }

    public void setPcode(String pcode) {
        this.pcode = pcode;
    }

    public String getCityname() {
        return cityname;
    }

    public void setCityname(String cityname) {
        this.cityname = cityname;
    }

    public String getCitycode() {
        return citycode;
    }

    public void setCitycode(String citycode) {
        this.citycode = citycode;
    }

    public String getAdname() {
        return adname;
    }

    public void setAdname(String adname) {
        this.adname = adname;
    }

    public String getAdcode() {
        return adcode;
    }

    public void setAdcode(String adcode) {
        this.adcode = adcode;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}
